package com.cpi.cornpp.purchaseorder.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.purchase.program.dto.PurchaseOrderDTO;
import com.cpi.cornpp.purchase.program.exception.PurchaseProgramException;
import com.cpi.cornpp.purchaseorder.dto.PurchaseOrderVersionDTO;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class PurchaseOrderVersionDAO extends DBDAO {
	private static final String SQL_INSERT_PURCHASE_ORDER_VERSION = "INSERT INTO purchases_orders_versions (purchase_order_id, transport_id, biweek_period_version_id, plant_id, code, code_number, capacity, corn_type_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";//$NON-NLS-1$
	private static final String SQL_UPDATE_PURCHASE_ORDER_VERSION = "UPDATE purchases_orders_versions SET purchase_order_id = ?, transport_id = ?, biweek_period_version_id = ?, plant_id = ?, code = ?, code_number = ?, capacity = ?, corn_type_id = ?";//$NON-NLS-1$
	private static final String SQL_UPDATE_POSTFIX_PURCHASE_ORDER_VERSION = "UPDATE purchases_orders_versions SET code_number = code_number+1 WHERE code = ? and code_number >= ?";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ORDER_VERSION = "SELECT purchase_order_version_id, purchase_order_id, transport_id, biweek_period_version_id, plant_id, code, code_number, capacity, corn_type_id, (select count(*) from po_balances_versions where purchase_order_version_id = purchases_orders_versions.purchase_order_version_id) as apply_balance FROM purchases_orders_versions";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ORDER_COMPLETE_VERSION = "SELECT purchases_orders_versions.purchase_order_version_id, purchase_order_id, purchases_orders_versions.transport_id, purchases_orders_versions.biweek_period_version_id, purchases_orders_versions.plant_id, code, code_number, purchases_orders_versions.capacity, purchases_orders_versions.corn_type_id, (select count(*) from po_balances_versions where purchase_order_version_id = purchases_orders_versions.purchase_order_version_id) as apply_balance, biweeks_periods.description, year, plants.short_name, quantity, (select short_name + ' ' + convert( varchar, year ) from months_periods_versions mpv, months_periods mp where ( mpv.month_period_id+(periods_number-1) ) = mp.month_period_id and month_period_version_id = start_block_period_id), (select short_name + ' ' + convert( varchar, year ) from months_periods_versions mpv, months_periods mp where ( mpv.month_period_id) = mp.month_period_id and month_period_version_id = start_block_period_id), regions.short_name, is_transit  FROM purchases_orders_versions INNER JOIN biweeks_periods_versions on biweeks_periods_versions.biweek_period_version_id = purchases_orders_versions.biweek_period_version_id and version_id = ? INNER JOIN biweeks_periods on biweeks_periods.biweek_period_id = biweeks_periods_versions.biweek_period_id LEFT JOIN plants on purchases_orders_versions.plant_id = plants.plant_id LEFT JOIN blocked_distributions on purchases_orders_versions.purchase_order_version_id = blocked_distributions.purchase_order_version_id LEFT JOIN regions_distributions on purchases_orders_versions.purchase_order_version_id = regions_distributions.purchase_order_version_id LEFT JOIN regions on regions_distributions.region_id = regions.region_id left join transports on purchases_orders_versions.transport_id = transports.transport_id";//$NON-NLS-1$
	private static final String SQL_INSERT_REGION_ORDER_VERSION = "INSERT INTO regions_distributions (purchase_order_version_id, region_id) select ?, region_id from regions where app_default = 1";//$NON-NLS-1$
	private static final String SQL_DELETE_BALANCES_VERSION = "DELETE FROM PO_BALANCES_VERSIONS WHERE PURCHASE_ORDER_VERSION_ID = ?";//$NON-NLS-1$
	private static final String SQL_INSERT_BALANCES = "insert into po_balances_versions (balance_id, balance_version_id,purchase_order_version_id,amount) select balance_id, null, ?, ? from balances where month_period_id = ?";//$NON-NLS-1$
	private static final String SQL_INSERT_BALANCES_VERSION = "insert into po_balances_versions (balance_id, balance_version_id,purchase_order_version_id,amount) select balance_id, balance_version_id, ?, ? from balances_versions where month_period_version_id = ?";//$NON-NLS-1$
	//private static final String SQL_SELECT_DRAFT_DISCOUNT = "select ( ? * isnull(pocv.amount,1) ) * ( ( pov.capacity * (1+(((tax1+tax2)/12)*?)) ) / (select qty from units where unit_id = 'BUS') ) FROM purchases_orders_versions pov inner join credits_lines on credit_line_id = 1 left join purchases_orders_costs_versions pocv on pov.purchase_order_version_id = pocv.purchase_order_version_id and cost_element_id = ? where pov.purchase_order_version_id = ?";//$NON-NLS-1$
	private static final String SQL_SELECT_DRAFT_DISCOUNT = "exec sp_draft_calc ?, ?";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ORDERS_FOR_MANUAL_CHARGES_SEARCH = "SELECT purchase_order_version_id, description + ' ' + convert( varchar, year ) AS period_description, code, code_number, capacity FROM	purchases_orders_versions pov inner join biweeks_periods_versions bpv on pov.biweek_period_version_id = bpv.biweek_period_version_id and bpv.version_id = ? inner join biweeks_periods bp on bpv.biweek_period_id = bp.biweek_period_id";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ORDERS_FOR_MANUAL_CHARGES = "SELECT pov.purchase_order_version_id, description + ' ' + convert( varchar, year ) AS period_description, code, code_number, capacity, mcd.amount FROM	purchases_orders_versions pov inner join biweeks_periods_versions bpv on pov.biweek_period_version_id = bpv.biweek_period_version_id and bpv.version_id = ? inner join biweeks_periods bp on bpv.biweek_period_id = bp.biweek_period_id";//$NON-NLS-1$	
	private static final String SQL_SELECT_PURCHASE_ORDERS_FOR_MANUAL_CHARGES_BY_ID = " inner join manual_charges_details mcd on mcd.purchase_order_version_id = pov.purchase_order_version_id and mcd.manual_charge_id = ?";//$NON-NLS-1$
	private static final String SQL_TRANSPORT_FILTER_FOR_MANUAL_CHARGES = "pov.transport_id = ?";//$NON-NLS-1$
	private static final String SQL_IS_TRANSIT_FILTER_FOR_MANUAL_CHARGES = "pov.is_transit = ?";//$NON-NLS-1$
	private static final String SQL_PERIODS_FILTER_FOR_MANUAL_CHARGES = "bp.start_date between (select start_date from biweeks_periods where biweek_period_id = ?) AND (select end_date from biweeks_periods where biweek_period_id = ?)";//$NON-NLS-1$
	private static final String NO_BUESHEL_ERROR = "label.error.message.draft.discount.no.bushel";//$NON-NLS-1$
	private static final String SP_DELETE_PURCHASE_ORDER_VERSION = "exec sp_deletePurchaseOrderVersion ?, ?";//$NON-NLS-1$
	private static final String SQL_SELECT_PURCHASE_ORDER_BY_FUTURE = "select pov.purchase_order_version_id, bp.description + ' ' + convert( varchar, fp.year ) AS period_description, code, code_number, pov.capacity, futures_order from biweeks_periods_versions bpv inner join biweeks_periods bp on bpv.biweek_period_id = bp.biweek_period_id inner join purchases_orders_versions pov on bpv.biweek_period_version_id = pov.biweek_period_version_id left join transports t on pov.transport_id = t.transport_id inner join futures_periods_versions fpv on bpv.version_id = fpv.version_id and fpv.future_period_version_id = ? inner join futures_periods fp on fpv.future_period_id = fp.future_period_id where bpv.version_id = ? and (bp.start_date + 1) between (fp.start_date) and (fp.end_date)  and pov.is_transit = 0 order by futures_order, bp.start_date, t.priority desc, pov.code, pov.code_number";//$NON-NLS-1$
	private static final String SP_MOVE_FUTURES_ORDER = "exec sp_po_futures_order_move ?, ?";//$NON-NLS-1$
	private static final String SP_DEFAULT_FUTURE_ORDER = "exec sp_po_futures_order_default ?";//$NON-NLS-1$
	private static final String SQL_UPDATE_FUTURE_SORT = "update purchases_orders_versions set futures_order = ? where purchase_order_version_id = ?";//$NON-NLS-1$
	private static final String SP_SET_FUTURE_ORDER = "exec sp_po_futures_order_set ?";//$NON-NLS-1$
	public int insertPurchaseOrderVersion(PurchaseOrderVersionDTO povDTO) throws PurchaseProgramException{
		int insertedRowId = -1;
		int insertedRows = -1;
		PreparedStatement pstm = null;		
		try{
			
			if (povDTO != null){
				pstm = prepareSQL(SQL_UPDATE_POSTFIX_PURCHASE_ORDER_VERSION, dsName);
				pstm.setString(1, povDTO.getCode());
				pstm.setInt(2, povDTO.getPostFix().intValue());
				executeUpdate(pstm);
				closeConnection(pstm.getConnection());
				
				pstm = prepareSQL(SQL_INSERT_PURCHASE_ORDER_VERSION, dsName);
				pstm.setObject(1, povDTO.getPurchaseOrderId());
				pstm.setInt(2, povDTO.getTransportId().intValue());
				pstm.setInt(3, povDTO.getBiWeekPeriodVersionId().intValue());
				pstm.setObject(4, povDTO.getPlantId());
				pstm.setString(5, povDTO.getCode());
				pstm.setInt(6, povDTO.getPostFix().intValue());
				pstm.setDouble(7, povDTO.getCapacity().doubleValue());
				pstm.setInt(8, povDTO.getCornTypeId().intValue());
				insertedRows = executeInsert(pstm);
				
				if (insertedRows == 1){
					insertedRowId = Integer.parseInt(getInsertedId(pstm.getConnection()));
					closeConnection(pstm.getConnection());
					
					pstm = prepareSQL(SQL_INSERT_REGION_ORDER_VERSION, dsName);
					pstm.setInt(1, insertedRowId);
					executeInsert(pstm);
				}
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_INSERT);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return insertedRowId;
	}
	
	public int updatePurchaseOrderVersionById(PurchaseOrderVersionDTO povDTO) throws PurchaseProgramException{
		int updatedRows = -1;
		PreparedStatement pstm = null;
		StringBuffer sql = null;
		
		try{
			
			if (povDTO != null){
				sql = new StringBuffer(SQL_UPDATE_PURCHASE_ORDER_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setObject(1, povDTO.getPurchaseOrderId());
				if(povDTO.getTransportId()!=null){
					pstm.setInt(2, povDTO.getTransportId().intValue());
				}else{
					pstm.setString(2, null);
				}
				pstm.setInt(3, povDTO.getBiWeekPeriodVersionId().intValue());
				pstm.setObject(4, povDTO.getPlantId());
				pstm.setString(5, povDTO.getCode());
				pstm.setObject(6, povDTO.getPostFix());
				pstm.setDouble(7, povDTO.getCapacity().doubleValue());
				pstm.setInt(8, povDTO.getCornTypeId().intValue());
				pstm.setObject(9, povDTO.getPurchaseOrderVersionId());
				
				updatedRows = executeUpdate(pstm);
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return updatedRows;
	}
	
	public void deletePurchaseOrderVersionById(int versionId, int purchaseOrderVersionId) throws PurchaseProgramException{
		PreparedStatement pstm = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try{
			pstm = prepareSQL(SP_DELETE_PURCHASE_ORDER_VERSION, dsName);
			pstm.setInt(1, versionId);
			pstm.setInt(2, purchaseOrderVersionId);
			executeUpdate(pstm);
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw ppe;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(DBDAO.FOREIGN_KEY_ERROR_CODE);
			if(sqlException.getErrorCode() == systemParameterBean.getIntValue()){
				purchaseProgramException = new PurchaseProgramException(DBDAO.FOREIGN_KEY_ERROR_KEY, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}else{
				purchaseProgramException = new PurchaseProgramException(sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}
			sqlException.printStackTrace(System.out);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	public void insertBalancesVersion(int monthPeriodId, Integer monthPeriodVersionId, int purchaseOrderVersionId, double amount) throws PurchaseProgramException{
		PreparedStatement preparedStatement = null;
		String strQuery = null;
		try{
			if( monthPeriodVersionId != null ){
				strQuery = SQL_INSERT_BALANCES_VERSION;
			}else{
				strQuery = SQL_INSERT_BALANCES;
			}
			preparedStatement = prepareSQL(strQuery, dsName);
			preparedStatement.setInt(1, purchaseOrderVersionId);
			preparedStatement.setDouble(2, amount);
			if( monthPeriodVersionId != null ){
				preparedStatement.setInt(3, monthPeriodVersionId.intValue());
			}else{
				preparedStatement.setInt(3, monthPeriodId);
			}
			executeUpdate(preparedStatement);
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw ppe;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	public void deleteBalancesVersion(int purchaseOrderVersionId)throws PurchaseProgramException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_DELETE_BALANCES_VERSION, dsName);
			preparedStatement.setInt(1, purchaseOrderVersionId);
			executeUpdate(preparedStatement);
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw ppe;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	public PurchaseOrderVersionDTO getPurchaseOrderVersionById(Integer povId) throws PurchaseProgramException{
		PurchaseOrderVersionDTO povDTO = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		
		try{
			
			if (povId != null){
				sql = new StringBuffer(SQL_SELECT_PURCHASE_ORDER_VERSION);
				
				sql.append(SQL_WHERE);
				sql.append(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setInt(1, povId.intValue());
				
				rs = executeQuery(pstm);
				povDTO = mapRow(rs, 1);
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return povDTO;
	}
	
	public List getPurchaseOrderVersions() throws PurchaseProgramException{
		List dtoList = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		dtoList = new ArrayList(0);
		
		try{
			
			pstm = prepareSQL(SQL_SELECT_PURCHASE_ORDER_VERSION, dsName);
			rs = executeQuery(pstm);
			dtoList = mapRows(rs);
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return dtoList;
	}
	
	public List getPurchaseOrderVersionByBiWeekPeriodVersionId(Integer versionId, Integer transportId, Integer isOrderPurchaseOrTransit,int orderField, boolean asc) throws PurchaseProgramException{
		List dtoList = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		PurchaseOrderVersionDTO purchaseOrderVersionDTO = null;
		dtoList = new ArrayList(0);
		try{
			if (versionId != null){
				sql = new StringBuffer(SQL_SELECT_PURCHASE_ORDER_COMPLETE_VERSION);
				//Se agrega al WHERE de la consulta el Id del Transporte
				if( transportId != null || isOrderPurchaseOrTransit != null ) {
					sql.append(SQL_WHERE);
					if(transportId != null){
						sql.append(PurchaseOrderVersionDTO.TRANSPORT_ID);
						sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);					
					}
					//Se verifica si es transito o no
					if(isOrderPurchaseOrTransit != null){
						if( transportId != null ){
							sql.append(SQL_AND);
						}
						sql.append(PurchaseOrderVersionDTO.IS_TRANSIT);
						sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
					}
				}
				//Ordenamiento por transito y por transporte
				sql.append(SQL_ORDER_BY);
				sql.append(PurchaseOrderVersionDTO.IS_TRANSIT);
				sql.append(SQL_DESCENDING_ORDER);
				sql.append(SQL_COMMA);
				switch (orderField){
					case 1: 	sql.append(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_BI_WEEK_PERIOD_VERSION);
								if (!asc){
									sql.append(SQL_ASCENDING_ORDER);
								} else {
									sql.append(SQL_DESCENDING_ORDER);
								}
								sql.append(SQL_COMMA);
								sql.append(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_PRIORITY);
								sql.append(SQL_DESCENDING_ORDER);
								break;
					case 2: 	sql.append(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_CODE);
								if (!asc){
									sql.append(SQL_ASCENDING_ORDER);
								} else {
									sql.append(SQL_DESCENDING_ORDER);
								}
								sql.append(SQL_COMMA);
								sql.append(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_NUMBER);
							break;
					case 3: sql.append(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_PLANT);
							break;
					case 4: sql.append(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_CAPACITY);
							break;
				}
				if(orderField != 1){
					if (!asc){
						sql.append(SQL_ASCENDING_ORDER);
					} else {
						sql.append(SQL_DESCENDING_ORDER);
					}
				}
				
				pstm = prepareSQL(sql.toString(), dsName);
				pstm.setInt(1, versionId.intValue());
				//Se agrega el filtro por transporte o por transito
				if(transportId != null && isOrderPurchaseOrTransit != null){
					pstm.setInt(2, transportId.intValue());
					pstm.setInt(3, isOrderPurchaseOrTransit.intValue());
				}else if(transportId != null && isOrderPurchaseOrTransit == null){
					pstm.setInt(2, transportId.intValue());
				}else if(isOrderPurchaseOrTransit != null && transportId == null){
					pstm.setInt(2, isOrderPurchaseOrTransit.intValue());
				}
																							
				rs = executeQuery(pstm);
				while(rs.next()){
					purchaseOrderVersionDTO = new PurchaseOrderVersionDTO();
					purchaseOrderVersionDTO.setPurchaseOrderVersionId((Integer) rs.getObject(1));
					purchaseOrderVersionDTO.setPurchaseOrderId((Integer)rs.getObject(2));
					purchaseOrderVersionDTO.setTransportId((Integer) rs.getObject(3));
					purchaseOrderVersionDTO.setBiWeekPeriodVersionId((Integer) rs.getObject(4));
					purchaseOrderVersionDTO.setPlantId((Integer) rs.getObject(5));
					purchaseOrderVersionDTO.setCode(rs.getString(6));
					purchaseOrderVersionDTO.setPostFix(new Integer(rs.getInt(7)));
					purchaseOrderVersionDTO.setCapacity(new Double(rs.getDouble(8)));
					purchaseOrderVersionDTO.setCornTypeId(new Integer(rs.getInt(9)));
					purchaseOrderVersionDTO.setApplyBalance(new Integer(rs.getInt(10)));
					purchaseOrderVersionDTO.setBiWeekPeriodVersionDescription(rs.getString(11));
					purchaseOrderVersionDTO.setBiWeekPeriodVersionYear((Integer) rs.getObject(12));
					purchaseOrderVersionDTO.setPlantShortName(rs.getString(13));
					purchaseOrderVersionDTO.setBlockQuantity(new Double(rs.getDouble(14)));
					purchaseOrderVersionDTO.setBlockStartDate(rs.getString(15));
					purchaseOrderVersionDTO.setBlockFinishDate(rs.getString(16));
					purchaseOrderVersionDTO.setRegionShortName(rs.getString(17));
					purchaseOrderVersionDTO.setIsTransit(rs.getInt(18));
					dtoList.add(purchaseOrderVersionDTO);
				}
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return dtoList;
	}
	
	public double getDraftDiscount(int purchaseOrderVersionId, int months)throws PurchaseProgramException{
		double totalFuturesUsed = 0;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean result = false;
		try{
			preparedStatement = prepareSQL(SQL_SELECT_DRAFT_DISCOUNT, dsName);
			preparedStatement.setInt(1, purchaseOrderVersionId);
			preparedStatement.setInt(2, months);
			resultSet = executeQuery(preparedStatement);
			if( resultSet.next() ){
				result = resultSet.getBoolean(1);
				if(result){
					totalFuturesUsed = resultSet.getDouble(3);
				}else{
					throw new PurchaseProgramException(NO_BUESHEL_ERROR, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
				}
			}
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return totalFuturesUsed;
	}
	
	private PurchaseOrderVersionDTO mapRow(ResultSet rs, int index) throws SQLException{
		PurchaseOrderVersionDTO dto = null;
		
		if ((rs != null) && (rs.absolute(index))){
			dto = new PurchaseOrderVersionDTO();
			
			dto.setPurchaseOrderVersionId((Integer) rs.getObject(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_ID));
			dto.setPurchaseOrderId((Integer)rs.getObject(PurchaseOrderVersionDTO.PURCHASE_ORDER_ID));
			dto.setTransportId((Integer) rs.getObject(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_TRANSPORT_ID));
			dto.setBiWeekPeriodVersionId((Integer) rs.getObject(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_BI_WEEK_PERIOD_VERSION_ID));
			dto.setPlantId((Integer) rs.getObject(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_PLANT_ID));
			dto.setCode(rs.getString(PurchaseOrderDTO.PURCHASE_ORDER_CODE));
			dto.setPostFix(new Integer(rs.getInt(PurchaseOrderDTO.PURCHASE_ORDER_CODE_NUMBER)));
			dto.setCapacity(new Double(rs.getDouble(PurchaseOrderVersionDTO.PURCHASE_ORDER_VERSION_CAPACITY)));
			dto.setCornTypeId(new Integer(rs.getInt(PurchaseOrderVersionDTO.PURCHASE_CORNTYPE_ID)));
			dto.setApplyBalance(new Integer(rs.getInt(PurchaseOrderVersionDTO.PURCHASE_APPLY_BALANCE_ID)));
		}
		
		return dto;
	}
	
	private List mapRows(ResultSet rs) throws SQLException{
		List dtoList = null;
		
		dtoList = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				dtoList.add(mapRow(rs, rs.getRow()));
			}
		}
		
		return dtoList;
	}
	/**
	 * Este metodo obtiene una lista para
	 * la busqueda de Ordenes de Compra de Version
	 * 
	 * @author Edgar Joao
	 * @param transportIdFilter
	 * @param transitTypeFilter
	 * @param initPeriodFilter
	 * @param endPeriodFilter
	 * @return
	 * @throws PurchaseProgramException
	 */
	public List getPurchasesOrdersVersionsList(int versionId, Integer transportIdFilter, Integer transitTypeFilter, Integer initPeriodFilter, Integer endPeriodFilter)throws PurchaseProgramException{
		List list = null;
		PurchaseOrderVersionDTO dto = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		try{
			list = new ArrayList(0);
			sql = new StringBuffer(0);
			sql.append(SQL_SELECT_PURCHASE_ORDERS_FOR_MANUAL_CHARGES_SEARCH);						
			sql.append(SQL_WHERE);
			//Se selecciono el transporte y verifica si es transito
			if(transportIdFilter != null && transitTypeFilter != null ){
				sql.append(SQL_TRANSPORT_FILTER_FOR_MANUAL_CHARGES);
				sql.append(SQL_AND);
				sql.append(SQL_IS_TRANSIT_FILTER_FOR_MANUAL_CHARGES);
				sql.append(SQL_AND);
			}
			//Se selecciono transporte
			if(transportIdFilter != null && transitTypeFilter == null ){
				sql.append(SQL_TRANSPORT_FILTER_FOR_MANUAL_CHARGES);
				sql.append(SQL_AND);
			}
			//Se selecciono si es transito 
			if(transitTypeFilter != null && transportIdFilter == null){
				sql.append(SQL_IS_TRANSIT_FILTER_FOR_MANUAL_CHARGES);
				sql.append(SQL_AND);
			}				
			//Filtro de los Periodos			
			sql.append(SQL_PERIODS_FILTER_FOR_MANUAL_CHARGES);
			
			pstm = prepareSQL(sql.toString(), dsName);						
			pstm.setInt(1, versionId);			
			if(transportIdFilter != null && transitTypeFilter != null ){
				pstm.setInt(2, transportIdFilter.intValue());
				pstm.setInt(3, transitTypeFilter.intValue());
				pstm.setInt(4, initPeriodFilter.intValue());
				pstm.setInt(5, endPeriodFilter.intValue());
			}
			if(transportIdFilter != null && transitTypeFilter == null ){
				pstm.setInt(2, transportIdFilter.intValue());				
				pstm.setInt(3, initPeriodFilter.intValue());
				pstm.setInt(4, endPeriodFilter.intValue());
			}
			if(transitTypeFilter != null && transportIdFilter == null){				
				pstm.setInt(2, transitTypeFilter.intValue());
				pstm.setInt(3, initPeriodFilter.intValue());
				pstm.setInt(4, endPeriodFilter.intValue());
			}
			if(transportIdFilter == null && transitTypeFilter == null ){
				pstm.setInt(2, initPeriodFilter.intValue());
				pstm.setInt(3, endPeriodFilter.intValue());
			}
			rs = executeQuery(pstm);
			while(rs.next()){
				dto = new PurchaseOrderVersionDTO();
				dto.setPurchaseOrderVersionId(new Integer(rs.getInt(1)));
				dto.setPeriodDescription(rs.getString(2));
				dto.setCode(rs.getString(3));
				dto.setCodeNumber(new Integer(rs.getInt(4)));
				dto.setCapacity(new Double(rs.getDouble(5)));
				list.add(dto);
			}			
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return list;
	}
	/**
	 * Lista que obtiene ordenes de compra por Version_id
	 * @param versionId
	 * @param manualChargeId
	 * @return
	 * @throws PurchaseProgramException
	 */
	public List getPurchasesOrdersVersionsListById(int versionId, int manualChargeId)throws PurchaseProgramException{
		List list = null;					
		PurchaseOrderVersionDTO dto = null;
		PreparedStatement pstm = null;		
		ResultSet rs = null;
		StringBuffer sql = null;		
		try{
			list = new ArrayList(0);
			sql = new StringBuffer(0);
			sql.append(SQL_SELECT_PURCHASE_ORDERS_FOR_MANUAL_CHARGES);
			sql.append(SQL_SELECT_PURCHASE_ORDERS_FOR_MANUAL_CHARGES_BY_ID);			
			pstm = prepareSQL(sql.toString(), dsName);						
			pstm.setInt(1, versionId);
			pstm.setInt(2, manualChargeId);
			rs = executeQuery(pstm);
			while(rs.next()){
				dto = new PurchaseOrderVersionDTO();
				dto.setPurchaseOrderVersionId(new Integer(rs.getInt(1)));
				dto.setPeriodDescription(rs.getString(2));
				dto.setCode(rs.getString(3));
				dto.setCodeNumber(new Integer(rs.getInt(4)));
				dto.setCapacity(new Double(rs.getDouble(5)));
				dto.setAmount(rs.getDouble(6));
				list.add(dto);
			}						
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return list;
	}
	
	/**
	 * Lista que obtiene ordenes de compra por periodo
	 * @param versionId
	 * @param futurePeriodVersionId
	 * @return
	 * @throws PurchaseProgramException
	 */
	public List purchasesOrdersVersionsByFuture(int versionId, int futurePeriodVersionId)throws PurchaseProgramException{
		List list = null;					
		PurchaseOrderVersionDTO dto = null;
		PreparedStatement pstm = null;		
		ResultSet rs = null;
		try{
			list = new ArrayList(0);
			pstm = prepareSQL(SQL_SELECT_PURCHASE_ORDER_BY_FUTURE, dsName);
			pstm.setInt(1, futurePeriodVersionId);
			pstm.setInt(2, versionId);
			rs = executeQuery(pstm);
			while(rs.next()){
				dto = new PurchaseOrderVersionDTO();
				dto.setPurchaseOrderVersionId(new Integer(rs.getInt(1)));
				dto.setPeriodDescription(rs.getString(2));
				dto.setCode(rs.getString(3));
				dto.setCodeNumber(new Integer(rs.getInt(4)));
				dto.setCapacity(new Double(rs.getDouble(5)));
				dto.setFutureSort(rs.getInt(6));
				list.add(dto);
			}						
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return list;
	}
	
	public void moveSort(int purchaseOrderVersionId, int direction)throws PurchaseProgramException{
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SP_MOVE_FUTURES_ORDER, dsName);
			pstm.setInt(1, purchaseOrderVersionId);
			pstm.setInt(2, direction);
			executeUpdate(pstm);
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	public void originalSort(int futurePeriodVersionId)throws PurchaseProgramException{
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SP_DEFAULT_FUTURE_ORDER, dsName);
			pstm.setInt(1, futurePeriodVersionId);
			executeUpdate(pstm);
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	public void saveSort(int futurePeriodVersionId, int purchaseOrderVersionId, int number)throws PurchaseProgramException{
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SQL_UPDATE_FUTURE_SORT, dsName);
			pstm.setInt(1, number);
			pstm.setInt(2, purchaseOrderVersionId);
			executeUpdate(pstm);
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}
	
	public void setSort(int futurePeriodVersionId)throws PurchaseProgramException{
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SP_SET_FUTURE_ORDER, dsName);
			pstm.setInt(1, futurePeriodVersionId);
			executeUpdate(pstm);
		} catch(PoolException poe){
			PurchaseProgramException ppe = null;
			poe.printStackTrace(System.out);
			ppe = new PurchaseProgramException(poe, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = null;
			sqle.printStackTrace(System.out);
			ppe = new PurchaseProgramException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw ppe;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}
}